﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using GtsServer.Common;
using System.Data;

namespace GtsServer.DAL
{
    public partial class PrizeTaskReocrdDAL
    {
        public PrizeTaskReocrdDAL() { }

        public DataSet GetClaimList(string userAccount)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount)
            };
            DataSet ds = DbHelperSQL.RunProcedure("GetClaimGoodsList", parms, "GetClaimGoodsList");
            return ds;
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select pr.Id,pr.UserID,isnull(ur.IMSI,'') as UserISMI,isnull(pz.Name,'')as PrizeName,pr.Prizeid,pr.Createtime,pr.EditTime,pr.Stuas,pr.TaskID,pr.Remark,pr.PrizeCount,pr.IsManuallyAdd,isnull(t.Name,'') TaskName from PrizeTaskReocrd pr ");
            strSql.Append(" left join [User] ur on pr.UserID=ur.ID left join Prize pz on pr.PrizeID=pz.ID left join Task t on pr.TaskID = t.ID ");
            if (UserID > 0)
            {
                strSql.Append(" where pr.Userid='" + UserID + "' ");
            }
            strSql.Append(" order by pr.CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetListByState(int state, string type, string value,string remark, int pageIndex, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from(");
            strSql.Append("select ur.UserAccount UserAccount, pr.Id,pr.UserID Userid,isnull(ur.IMSI,'') as UserIMSI,isnull(ur.IMEI,'') UserImei,isnull(ur.Mac,'') UserMac ,isnull(pz.Name,'')as PrizeName,pr.Prizeid,pr.Createtime,pr.EditTime,pr.Stuas,pr.TaskID,pr.Remark,pr.PrizeCount,pr.IsManuallyAdd,isnull(t.Name,'') TaskName,ROW_NUMBER() OVER(order by pr.CreateTime desc) as RowNumber from PrizeTaskReocrd pr ");
            strSql.Append(" left join [User] ur on pr.UserID=ur.ID left join Prize pz on pr.PrizeID=pz.ID left join Task t on pr.TaskID = t.ID ");
            strSql.Append(" where pr.Stuas='" + state + "' ");
            if (!string.IsNullOrEmpty(value))
                strSql.Append(" and ur." + type + " ='" + value + "'");
            if (!string.IsNullOrEmpty(remark))
                strSql.Append(" and pr.Remark ='" + remark + "'");
            strSql.Append(" ) a where  RowNumber BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");


            strSql.Append(" select COUNT(1) count from PrizeTaskReocrd pr  left join [User] ur on pr.UserID=ur.ID left join Prize pz on pr.PrizeID=pz.ID left join Task t on pr.TaskID = t.ID ");
            strSql.Append(" where pr.Stuas='" + state + "' ");
            if (!string.IsNullOrEmpty(remark))
                strSql.Append(" and pr.Remark ='" + remark + "'");
            if (!string.IsNullOrEmpty(value))
                strSql.Append(" and ur." + type + " ='" + value + "';select ID  from [user] where " + type + " = '" + value + "'");

            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(GtsServer.Model.PrizeTaskReocrd model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PrizeTaskReocrd(");
            strSql.Append("Userid,Prizeid,Createtime,Stuas,TaskID,EditTime,Remark,PrizeCount,IsManuallyAdd)");
            strSql.Append(" values (");
            strSql.Append("@Userid,@Prizeid,@Createtime,@Stuas,@TaskID,@EditTime,@Remark,@PrizeCount,@IsManuallyAdd)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Userid", SqlDbType.Int,4),
					new SqlParameter("@Prizeid", SqlDbType.Int,4),
					new SqlParameter("@Createtime", SqlDbType.DateTime),
					new SqlParameter("@Stuas", SqlDbType.Int,4),
					new SqlParameter("@TaskID", SqlDbType.Int,4),
					new SqlParameter("@EditTime", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.VarChar,200),
					new SqlParameter("@PrizeCount", SqlDbType.Int,4),
					new SqlParameter("@IsManuallyAdd", SqlDbType.Bit,1)};
            parameters[0].Value = model.Userid;
            parameters[1].Value = model.Prizeid;
            parameters[2].Value = model.Createtime;
            parameters[3].Value = model.Stuas;
            parameters[4].Value = model.TaskID;
            parameters[5].Value = model.EditTime;
            parameters[6].Value = model.Remark;
            parameters[7].Value = model.PrizeCount;
            parameters[8].Value = model.IsManuallyAdd;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(GtsServer.Model.PrizeTaskReocrd model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PrizeTaskReocrd set ");
            strSql.Append("Remark=@Remark,");
            strSql.Append("TaskID=@TaskID,");
            strSql.Append("Prizeid=@Prizeid,EditTime=@EditTime,");
            strSql.Append("PrizeCount=@PrizeCount");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Remark", SqlDbType.VarChar,200),
                    new SqlParameter("@TaskID", SqlDbType.Int,4),
                    new SqlParameter("@Prizeid", SqlDbType.Int,4),
					new SqlParameter("@PrizeCount", SqlDbType.Int,4),
                    new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@EditTime", SqlDbType.DateTime)};
            parameters[0].Value = model.Remark;
            parameters[1].Value = model.TaskID;
            parameters[2].Value = model.Prizeid;
            parameters[3].Value = model.PrizeCount;
            parameters[4].Value = model.Id;
            parameters[5].Value = model.EditTime;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PrizeTaskReocrd ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
